﻿-- Script n°006: "comment" table.

BEGIN;

-- Sequence on comment id
DROP TABLE IF EXISTS comment;
DROP SEQUENCE IF EXISTS seq_comment;

CREATE SEQUENCE seq_comment
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

ALTER TABLE seq_comment OWNER TO chelioudbdev;
GRANT ALL ON TABLE seq_comment TO chelioudbdev;
GRANT SELECT, UPDATE ON TABLE seq_comment TO cheliouwebdev;

-- Comment table


CREATE TABLE comment (
   id                   NUMERIC              NOT NULL	DEFAULT nextval('seq_comment'::regclass),
   date                 TIMESTAMP            NOT NULL,
   content              TEXT                 NOT NULL,
   memberid            NUMERIC              NOT NULL,
   photoid             NUMERIC              NOT NULL,
   CONSTRAINT pk_comment PRIMARY KEY (id)
)WITH (
  OIDS=FALSE
);

ALTER TABLE comment
   ADD CONSTRAINT fk_comment_member FOREIGN KEY (memberid)
      REFERENCES member (id)
      ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE comment
   ADD CONSTRAINT fk_comment_photo FOREIGN KEY (photoid)
      REFERENCES photo (id)
      ON DELETE RESTRICT ON UPDATE RESTRICT;
      
ALTER TABLE comment OWNER TO chelioudbdev;
GRANT ALL ON TABLE comment TO chelioudbdev;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE comment TO cheliouwebdev;

COMMIT;
